package com.bookmanger.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.bookmanger.entity.Book;
import com.bookmanger.entity.vo.BookCategoryCountVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author huangfuhao
 * @Date 2024/10/26
 */
@Mapper
public interface BookMapper extends BaseMapper<Book> {

    @Select("""
            SELECT
                c.category_name AS category,       -- 分类名称
                COUNT(b.id) AS count               -- 分类下书籍数量
            FROM book b
            LEFT JOIN category c ON b.category_id = c.id  -- 连接 category 表
            GROUP BY c.category_name               -- 按分类名称分组
            """)
    List<BookCategoryCountVO> getBookCategoryCount();

}
